package com.registerserver.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.registerserver.dto.CourseRegisteredInfo;
import com.registerserver.model.Course;
import com.registerserver.model.Studying;
import com.registerserver.model.Subject;

public class CourseDaoImpl extends ConnectionFactory implements CourseDao{
	
	private Connection con=null;
	
	public CourseDaoImpl() {
		con = ConnectionFactory.createConnection();
	}
	
	// lấy thông tin các môn học phần của 1 môn học , cái này có thể bỏ 
	@Override
	public List<Course> getCourseOfSubject(String studentID, String semester, String subjectID) throws SQLException {
		List<Course> listCourse = new ArrayList<>();
		String sql = "{call getCourseOfSubject(?,?,?)}";
		CallableStatement call = con.prepareCall(sql);
		call.setString(1, studentID);
		call.setString(2, semester);
		call.setString(3, subjectID);
		ResultSet result = call.executeQuery();
		if (result!=null){
			while(result.next()){
				Course course = new Course();
				course.setCourseID(result.getString("CourseID"));
				course.setClassRoom(result.getString("ClassRoom"));
				course.setSubjectID(result.getString("SubjectID"));
				course.setState(result.getBoolean("State"));
				course.setMinOfStudent(result.getInt("MinOfStudent"));
				course.setTeacherID(result.getString("TeacherID"));
				course.setSemesterID(result.getString("SemesterID"));
				listCourse.add(course);
			}
		}
		return listCourse;
	}
	
	// thêm 1 môn học phần mới_ dành cho giáo vụ
	@Override
	public boolean addNewCourse(Course course) throws SQLException {
		String sql = "call addNewCourse(?,?,?,?,?,?)";
		CallableStatement call = con.prepareCall(sql);
		call.setString(1, course.getCourseID());
		call.setString(2,course.getSemesterID());
		call.setString(3,course.getSubjectID());
		call.setString(4, course.getClassRoom());
		call.setString(5, course.getTeacherID());
		call.setDate(6, (Date)course.getEndDate());
		call.setDate(7, (Date)course.getEndDate());
		
		return false;
	}

	//xóa môn học phần _ dành cho giáo vụ
	@Override
	public boolean deleteCourse(String courseID) {
		// TODO Auto-generated method stub
		return false;
	}
/***
 * sub.SubjectID, cou.CourseID, sub.SubjectName, sub.Units, cou.ClassRoom,cou.State,
 cou.MinOfStudent,tea.TeacherFirstName,tea.TeacherLastName, stu.RegisterDate
 */
	// lấy thông tin các môn học vừa đăng kí trong kì vừa rồi của sinh viên
	@Override
	public List<CourseRegisteredInfo> getCoursesInfoRegisteredSInSemester(String studentID,
			String semester) throws SQLException {
		
		List<CourseRegisteredInfo> list = new ArrayList<>();
		String sql = "call getCourseInfoRegisteredInSemester(?,?)";
		CallableStatement call = con.prepareCall(sql);
		call.setString(1, studentID);
		call.setString(2, semester);
		ResultSet result= call.executeQuery();
		while(result.next()){
			Course course = new Course(); 
			course.setCourseID(result.getString("CourseID"));
			
			Subject subject = new Subject();
			subject.setSubjectID(result.getString("SubjectID"));
			subject.setSubjectName(result.getString("SubjectName"));
			subject.setUnits(result.getInt("Units"));
			Studying studying = new Studying();
			studying.setDateOfRegister(result.getDate("RegisterDate"));
			
			CourseRegisteredInfo courseInfo = new CourseRegisteredInfo();
			courseInfo.setCourseID(course.getCourseID());
			courseInfo.setSubjectID(subject.getSubjectID());
			courseInfo.setSubjectName(subject.getSubjectName());
			courseInfo.setDateOfRegister((Date)studying.getDateOfRegister());
			courseInfo.setUnits(subject.getUnits());
			
			list.add(courseInfo);
		}
		return list;
	}

	@Override
	public Course getCourseInfoByID(String courseID) throws SQLException {
		Course course = new Course();
		String sql = "call getCourseInfoByID(?)";
		CallableStatement call = con.prepareCall(sql);
		call.setString(1, courseID);
		ResultSet result = call.executeQuery();
		if(result.next()){
			course.setCourseID(result.getString("CourseID"));
			course.setSemesterID(result.getString("SemesterID"));
			course.setClassRoom(result.getString("ClassRoom"));
			course.setEndDate(result.getDate("EndDate"));
			course.setMinOfStudent(result.getInt("MinOfStudent"));
			course.setStartedDate(result.getDate("StartedDate"));
			course.setState(result.getBoolean("State"));
			course.setTeacherID(result.getString("TeacherID"));
		}
		return course;
	}

	@Override
	public Date getExpiredDate(String courseID) throws SQLException {
		String sql = "call getExpiredDate(?)";
		 CallableStatement call = con.prepareCall(sql);
		 call.setString(1, courseID);
		 ResultSet result = call.executeQuery();
		 Date expiredDate = new Date(20010101);
		 if (result.next()){
			 expiredDate = result.getDate("ExpiredDate");
		 }
		 return expiredDate;
	}
}
